package com.wanmait.travel.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.wanmait.travel.util.DbHelper;
import com.wanmait.travel.vo.Scenic;

public class ScenicInfoDAO
{
	private static ScenicInfoDAO scenicInfoDAO;
	public static ScenicInfoDAO getDAO() {
		if(scenicInfoDAO==null) {
			scenicInfoDAO =new ScenicInfoDAO();
			return scenicInfoDAO;
		}
		return scenicInfoDAO;
	}
	private ScenicInfoDAO() {}

	
	public  List<Scenic>findByCityId(int id) 
	{
		
		ArrayList<Scenic> scenics= new ArrayList<Scenic>();
		String sql="SELECT scenic.*,city.* FROM scenic LEFT JOIN city ON scenic.city_id=city.id WHERE city.id=?";
		Connection  connection =  DbHelper.getConn();
		PreparedStatement  ps = null;
		ResultSet rs =null;
		try {
			ps =connection.prepareStatement(sql);
			ps.setInt(1, id);
			rs=ps.executeQuery();
		while(rs.next())
		{
			Scenic scenic =new  Scenic();
			scenic.setId(rs.getInt("id"));
			scenic.setName(rs.getString("name"));
			scenic.setLocation(rs.getString("location"));
			scenic.setOpenTime(rs.getString("open_time"));
			scenic.setCloseTime(rs.getString("close_time"));
			scenic.setIntroPic(rs.getString("intro_pic"));
			scenic.setTen(rs.getDouble("ten"));
			scenic.setLatitude(rs.getDouble("latitude"));
			
			scenics.add(scenic);
		}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally {
			DbHelper.close(rs, ps, connection);
		}
		return scenics;

	}
	
	
	
	
	public Scenic scenicTL(int scenicId) {
		String sql="SELECT scenic.* FROM scenic WHERE id=?";
		Scenic scenic=null;
		Connection  connection =  DbHelper.getConn();
		PreparedStatement  ps = null;
		ResultSet rs =null;
		try {
			ps =connection.prepareStatement(sql);
			ps.setInt(1, scenicId);
			rs=ps.executeQuery();
		while(rs.next())
		{
			scenic =new  Scenic();
			scenic.setId(rs.getInt("id"));
			scenic.setName(rs.getString("name"));
			scenic.setLocation(rs.getString("location"));
			scenic.setOpenTime(rs.getString("open_time"));
			scenic.setCloseTime(rs.getString("close_time"));
			scenic.setIntroPic(rs.getString("intro_pic"));
			scenic.setTen(rs.getDouble("ten"));

			scenic.setLatitude(rs.getDouble("latitude"));
		}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally {
			DbHelper.close(rs, ps, connection);
		}
		return scenic;
	}
	
}
